﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WindowsFormsApplication7.DaTa;
using System.Data;
using System.Data.SqlClient;

namespace WindowsFormsApplication7.Control
{
    class Product
    {
        public int ProductID { get; set; }
        public string ProductName { get; set; }
        public int SupplierID { get; set; }
        public int CategoryID { get; set; }
        public string QuantityPerUnit { get; set; }
        public double UnitPrice { get; set; }
        public int UnitsInStock { get; set; }
        public int UnitsOnOrder { get; set; }
        public int ReorderLevel { get; set; }
        public string Discontinued { get; set; }

        ConnectDB con =new ConnectDB();
        public Product()
        {
            con.GetConect();
        }
        //nạp chồng phương thức select
        public DataTable Select()
        {
            return con.GetData("select ProductID,ProductName,CategoryName,CompanyName,UnitPrice,UnitsInStock from Products inner join Suppliers on Products.SupplierID=Suppliers.SupplierID inner join Categories on Products.CategoryID=Categories.CategoryID");
        }
        public DataTable Select(string sql)
        {
            return con.GetData(sql);
        }
        public DataTable Select(int id)
        {
            return con.GetData("select * from Products where ProductID = " + id);
        }
        public void Update(Product product)
        {
            SqlCommand cmd = new SqlCommand("UPDATE Products SET  ProductName = @ProductName, CategoryID = @CategoryID, SupplierID = @SupplierID, UnitPrice = @UnitPrice WHERE ProductID = @ProductID");
            //Khai báo các parameters >>>> các prameter này có tác dụng trong SQL injection
            cmd.Parameters.Add("@ProductName", SqlDbType.NChar, 40);
            cmd.Parameters.Add("@CategoryID", SqlDbType.Int);
            cmd.Parameters.Add("@UnitPrice", SqlDbType.Real);
            cmd.Parameters.Add("@SupplierID", SqlDbType.Int);
            cmd.Parameters.Add("@ProductID", SqlDbType.Int);
            //gán giá trị cho para
            cmd.Parameters["@ProductName"].Value = ProductName;
            cmd.Parameters["@CategoryID"].Value = CategoryID;
            cmd.Parameters["@UnitPrice"].Value = UnitPrice;
            cmd.Parameters["@SupplierID"].Value = SupplierID;
            cmd.Parameters["@ProductID"].Value = ProductID;
            con.SetDataNotInjection(cmd);

        }

        public void Insert(Product product)
        {
            SqlCommand cmd = new SqlCommand("insert into Products(ProductName, SupplierID, CategoryID,UnitPrice) values(@ProductName,@SupplierID,@CategoryID,@UnitPrice)");

            cmd.Parameters.Add("@ProductName", SqlDbType.NChar, 40);
            cmd.Parameters.Add("@CategoryID", SqlDbType.Int);
            cmd.Parameters.Add("@UnitPrice", SqlDbType.Real);
            cmd.Parameters.Add("@SupplierID", SqlDbType.Int);

            cmd.Parameters["@ProductName"].Value = ProductName;
            cmd.Parameters["@CategoryID"].Value = CategoryID;
            cmd.Parameters["@UnitPrice"].Value = UnitPrice;
            cmd.Parameters["@SupplierID"].Value = SupplierID;
            con.SetDataNotInjection(cmd);
        }
        public void Delete(Product product)
        {
            SqlCommand cmd=new SqlCommand("DELETE FROM Products WHERE ProductID = " + @ProductID);
            cmd.Parameters.Add("@ProductID", SqlDbType.Int);
            cmd.Parameters["@ProductID"].Value = ProductID;
            con.SetDataNotInjection(cmd);
        }
        ///còn nếu không thích có thể viết kiểu ghép chuỗi
        ///
        public void Update1(Product pr)
        {
            con.SetData("update Products set ProductName='" + ProductName + "',SupplierID=" + SupplierID + ",CategoryID=" + CategoryID + ",QuantityPerUnit='" + QuantityPerUnit + "',UnitPrice=" + UnitPrice + " where ProductID =" + ProductID + "");
        }
        public void Delete1(int id)
        {
            ProductID = id;
            con.SetData("Delete from Products where ProductID=" + ProductID + "");
        }
        public void Insert1(Product pr)
        {
            con.SetData("Insert into Products(ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice) values('" + ProductName + "'," + SupplierID + "," + CategoryID + ",'" + QuantityPerUnit + "'," + UnitPrice + ")");
        }
    }
}
